﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;

/// <summary>
/// infoDao 的摘要说明
/// </summary>
public class InfoDao
{
    //获取数据库连接串
    private static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MySQLConnectionString"].ConnectionString;

    /// <summary>
    /// 
    ///获取Info表中所有的记录
    /// </summary>
    /// <returns></returns>
    public static List<Info> FindAll() {
        List<Info> data = new List<Info>();
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            string sql = "select * from tb_info";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql,conn);
            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read()) {
                Info info = new Info();
                info.Id = Convert.ToInt64(rdr["id"]);
                info.Title = Convert.ToString(rdr["title"]);
                info.Content = Convert.ToString(rdr["content"]);
                info.PublishTime = Convert.ToDateTime(rdr["publish_time"]);
                info.Author = Convert.ToString(rdr["author"]);
                info.ClickNumber = Convert.ToInt32(rdr["click_number"]);
                info.CategoryMark = Convert.ToString(rdr["category_mark"]);
                data.Add(info);
            }
            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }

    /// <summary>
    /// 分页获取Info表中所有的记录
    /// </summary>
    /// <param name="start">起始位置</param>
    /// <param name="num">获取的记录数</param>
    /// <returns>获取list集合</returns>
    public static List<Info> FindAll(int start,int num) {
        List<Info> data = new List<Info>();
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //sql语句字符串拼接形式
            string sql = "select * from tb_info limit "+start+","+num;
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Info info = new Info();
                info.Id = Convert.ToInt64(rdr["id"]);
                info.Title = Convert.ToString(rdr["title"]);
                info.Content = Convert.ToString(rdr["content"]);
                info.PublishTime = Convert.ToDateTime(rdr["publish_time"]);
                info.Author = Convert.ToString(rdr["author"]);
                info.ClickNumber = Convert.ToInt32(rdr["click_number"]);
                info.CategoryMark = Convert.ToString(rdr["category_mark"]);
                data.Add(info);
            }
            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }

    /// <summary>
    /// 按照id获取记录
    /// </summary>
    /// <param name="id">信息的内部id序号</param>
    /// <returns>指定的信息记录</returns>
    public static Info Find(long id) {
        Info data = new Info();

        MySqlConnection conn = new MySqlConnection(connStr);

        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "select * from tb_info where id=@id";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@id",id);

            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                data.Id = Convert.ToInt64(rdr["id"]);
                data.Title = Convert.ToString(rdr["title"]);
                data.Content = Convert.ToString(rdr["content"]);
                data.PublishTime = Convert.ToDateTime(rdr["publish_time"]);
                data.Author = Convert.ToString(rdr["author"]);
                data.ClickNumber = Convert.ToInt32(rdr["click_number"]);
                data.CategoryMark = Convert.ToString(rdr["category_mark"]);
            }
            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Find方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Find方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }

    /// <summary>
    /// 更新一条记录
    /// </summary>
    /// <param name="data"></param>
    public static void Update(Info data) {

        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "update tb_info set title=@title,content=@content,author=@author where id=@id";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@title", data.Title);
            cmd.Parameters.AddWithValue("@content", data.Content);
            cmd.Parameters.AddWithValue("@author", data.Author);
            cmd.Parameters.AddWithValue("@id", data.Id);
            //无返回结果集
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Update方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Update方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

    }

    /// <summary>
    /// 删除一条记录
    /// </summary>
    /// <param name="id"></param>
    public static void Delete(long id) {
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "delete from tb_info where id=@id";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@id",id);
            //无返回结果集
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Delete方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Delete方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }

    /// <summary>
    /// 插入一条记录
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    public static long Insert(Info data) {
        long insertId = 0;
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "insert into tb_info " +
                            "(title,content,publish_time,author,click_number,category_mark)" +
                            "values(@title,@content,@publish_tme,@author,@click_number,@category_mark)";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@title", data.Title);
            cmd.Parameters.AddWithValue("@content", data.Content);
            cmd.Parameters.AddWithValue("@publish_tme", data.PublishTime);
            cmd.Parameters.AddWithValue("@author", data.Author);
            cmd.Parameters.AddWithValue("@click_number", data.ClickNumber);
            cmd.Parameters.AddWithValue("@category_mark", data.CategoryMark);
            //无返回结果集
            cmd.ExecuteNonQuery();
            //返回插入的记录的数据库Id
            insertId = cmd.LastInsertedId;
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Insert方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Insert方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
        return insertId;
    }
}